-- EDGE tables SCRIPT

CREATE TABLE [dbo].[Document](
	[docid] [int] NOT NULL,
	[name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED 
(
	[docid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Path](
	[path_id] [int] NOT NULL,
	[path] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Path] PRIMARY KEY CLUSTERED 
(
	[path_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Edge](
	[docid] [int] NOT NULL,
	[id] [int] NOT NULL,
	[parent_id] [int] NULL,
	[end_desc_id] [int] NULL,
	[path_id] [int] NULL,
	[value] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[Attribute](
	[docid] [int] NOT NULL,
	[parent_id] [int] NOT NULL,
	[path_id] [int] NOT NULL,
	[value] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[TextContent](
	[docid] [int] NOT NULL,
	[id] [int] NOT NULL,
	[parent_id] [int] NULL,
	[end_desc_id] [int] NULL,
	[path_id] [int] NULL,
	[value] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[AttrContent](
	[docid] [int] NOT NULL,
	[parent_id] [int] NOT NULL,
	[path_id] [int] NOT NULL,
	[value] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


--INDEXES

CREATE UNIQUE NONCLUSTERED INDEX [IX_Path] ON [dbo].[Path] 
(
	[path] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


CREATE UNIQUE CLUSTERED INDEX [IX_Edge_ID] ON [dbo].[Edge] 
(
	[docid] ASC,
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Edge] ON [dbo].[Edge] 
(
	[parent_id] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Edge_1] ON [dbo].[Edge] 
(
	[end_desc_id] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Edge_2] ON [dbo].[Edge] 
(
	[path_id] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Edge_3] ON [dbo].[Edge] 
(
	[path_id] ASC,
	[value] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


CREATE UNIQUE CLUSTERED INDEX [IX_Attribute_ID] ON [dbo].[Attribute] 
(
	[docid] ASC,
	[parent_id] ASC,
	[path_id] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Attribute] ON [dbo].[Attribute] 
(
	[path_id] ASC,
	[value] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


CREATE UNIQUE CLUSTERED INDEX [IX_TextContent_ID] ON [dbo].[TextContent] 
(
	[docid] ASC,
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [IX_AttrContent_ID] ON [dbo].[AttrContent] 
(
	[docid] ASC,
	[parent_id] ASC,
	[path_id] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]


/****** Statistics Table     ******/
/****** Object:  Table [dbo].[PathCount_ValueSelectivity]     ******/
CREATE TABLE [dbo].[PathCount_ValueSelectivity](
	[path_id] [int] NULL,
	[CP] [int] NULL,
	[VP] [int] NULL
) ON [PRIMARY]

/****** Object:  Table [dbo].[TotalPathCount]   ******/
CREATE TABLE [dbo].[TotalPathCount](
	[T] [int] NULL
) ON [PRIMARY]
